﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using DTO;


namespace DAO
{
    public class PhongDAO:DataProvider
    {
        public List<PhongDTO> GetAll()
        {
            SqlConnection connection = MoKetNoi();
            string sql = "SELECT * FROM Phong";
            SqlCommand command = new SqlCommand(sql, connection);
            SqlDataReader reader = command.ExecuteReader();
            List<PhongDTO> dskh = new List<PhongDTO>();
            PhongDTO pb;
            while (reader.Read())
            {
                pb = new PhongDTO();
                pb.Maphong = reader.GetString(0);
                pb.Tenphong = reader.GetString(1);
                pb.Mota = reader.GetString(2);
                pb.Tinhtrang = reader.GetString(3);
                pb.Maloaiphong = reader.GetString(4);
                dskh.Add(pb);
            }
            DongKetNoi(connection);
            return dskh;
        }
        public bool Insert(PhongDTO lp)
        {
            bool kq = true;
            SqlConnection connection = MoKetNoi();
            string sql = "insert into Phong(Ma_Phong,TenPhong,Mota,TinhTrang,Ma_LoaiPhong) values('" + lp.Maphong + "',N'" + lp.Tenphong + "',N'" + lp.Mota + "','" + lp.Tinhtrang + "','" + lp.Maloaiphong + "')";
            SqlCommand command = new SqlCommand(sql, connection);
            int x = command.ExecuteNonQuery();
            if (x <= 0)
                kq = false;
            DongKetNoi(connection);
            return kq;
        }
        public bool Update(PhongDTO p)
        {
            bool kq = true;
            SqlConnection connection = MoKetNoi();
            string sql = "update Phong set TenPhong = (N'" + p.Tenphong + "'),Mota = (N'" + p.Mota + "'),TinhTrang = ('" + p.Tinhtrang + "'),Ma_LoaiPhong = ('" + p.Maloaiphong + "') where Ma_Phong = ('" + p.Maphong + "')";
            SqlCommand command = new SqlCommand(sql, connection);
            int x = command.ExecuteNonQuery();
            if (x <= 0)
                kq = false;
            DongKetNoi(connection);
            return kq;
        }
        public bool Delete(PhongDTO lp)
        {
            bool kq = true;
            SqlConnection connection = MoKetNoi();
            string sql = "delete from Phong where Ma_Phong = ('" + lp.Maphong + "')";
            SqlCommand command = new SqlCommand(sql, connection);
            int x = command.ExecuteNonQuery();
            if (x <= 0)
                kq = false;
            DongKetNoi(connection);
            return kq;
        }
        public List<PhongDTO> SelectLikeMa(PhongDTO p)
        {

            SqlConnection connection = MoKetNoi();
            string sql = "select * from Phong where Ma_Phong = ('" + p.Maphong + "')";
            SqlCommand command = new SqlCommand(sql, connection);
            SqlDataReader reader = command.ExecuteReader();
            List<PhongDTO> dsp = new List<PhongDTO>();
            PhongDTO pb;
            while (reader.Read())
            {
                pb = new PhongDTO();
                pb.Maphong = reader.GetString(0);
                pb.Tenphong = reader.GetString(1);
                pb.Mota = reader.GetString(2);
                pb.Tinhtrang = reader.GetString(3);
                pb.Maloaiphong = reader.GetString(4);
                dsp.Add(pb);
            }
            DongKetNoi(connection);
            return dsp;
        }
        public List<PhongDTO> LayPhongTheoMaLoaiPhong(string MaLoaiPhong)
        {
            List<PhongDTO> dsp = new List<PhongDTO>();
            SqlConnection connection = MoKetNoi();
            string sql = "SELECT * FROM Phong WHERE Ma_LoaiPhong = '" + MaLoaiPhong.Trim() + "'";
            SqlCommand command = new SqlCommand(sql, connection);
            SqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                PhongDTO p = new PhongDTO();
                p.Maphong = reader.GetString(0);
                p.Tenphong = reader.GetString(1);
                p.Mota = reader.GetString(2);
                p.Tinhtrang = reader.GetString(3);
                p.Maloaiphong = MaLoaiPhong;
                dsp.Add(p);
            }
            return dsp;
        }

    }
}
